package com.ruoyi.dm.component.manager;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.dm.domain.BO.TrackDataBO;
import com.ruoyi.trackapply.domain.CollectGnssPO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 精密轨道
 * 根据上传的卫星,自动建表,并存数据
 */
@Slf4j
@Component
public class TrackDataManager {


    @Autowired
    JdbcTemplate jdbcTemplate;


    //监测
    private String checkSql ="select 1 from %s limit 1";

    //建索引
    private String indexSql ="CREATE INDEX %s_TIME ON PIESAT_DGDQ.%s (OBS_TIME)";

    //建索引
    private String creatDbSql = "CREATE TABLE %s ( ID SERIAL PRIMARY KEY , POSITION_X DECIMAL,POSITION_Y DECIMAL,POSITION_Z DECIMAL," +
            "SPEED_VX DECIMAL,SPEED_VY DECIMAL,SPEED_VZ DECIMAL,OBS_TIME TIMESTAMP)" +
            "PARTITION BY RANGE (OBS_TIME) ("+
            "PARTITION P1 VALUES LESS THAN ('2000-01-01')," +
            "PARTITION P2 VALUES LESS THAN ('2002-01-01')," +
            "PARTITION P3 VALUES LESS THAN ('2004-01-01')," +
            "PARTITION P4 VALUES LESS THAN ('2006-01-01')," +
            "PARTITION P5 VALUES LESS THAN ('2008-01-01')," +
            "PARTITION P6 VALUES LESS THAN ('2010-01-01')," +
            "PARTITION P7 VALUES LESS THAN ('2012-01-01')," +
            "PARTITION P8 VALUES LESS THAN ('2014-01-01')," +
            "PARTITION P9 VALUES LESS THAN ('2016-01-01')," +
            "PARTITION P10 VALUES LESS THAN ('2018-01-01')," +
            "PARTITION P11 VALUES LESS THAN ('2020-01-01')," +
            "PARTITION P12 VALUES LESS THAN ('2022-01-01')," +
            "PARTITION P13 VALUES LESS THAN ('2024-01-01')," +
            "PARTITION P14 VALUES LESS THAN ('2026-01-01')," +
            "PARTITION P15 VALUES LESS THAN ('2028-01-01')," +
            "PARTITION P16 VALUES LESS THAN ('2030-01-01')," +
            "PARTITION P17 VALUES LESS THAN ('2032-01-01')," +
            "PARTITION P18 VALUES LESS THAN ('2034-01-01')," +
            "PARTITION P19 VALUES LESS THAN ('2036-01-01')," +
            "PARTITION P20 VALUES LESS THAN ('2100-01-01') )";

    //备注
    private String commont1  = "comment on column %s.POSITION_X  IS '位置'";
    private String commont2  = "comment on column %s.POSITION_Y  IS '位置'";
    private String commont3  = "comment on column %s.POSITION_Z  IS '位置'";
    private String commont4  = "comment on column %s.SPEED_VX  IS '速度'";
    private String commont5  = "comment on column %s.SPEED_VY  IS '速度'";
    private String commont6  = "comment on column %s.SPEED_VZ  IS '速度'";
    private String commont7  = "comment on column %s.OBS_TIME  IS '观测时间'";

    private String commont8  = "comment on table %s  IS '卫星GNSS记录表'";

    private String columnSql = "insert into %s (POSITION_X,POSITION_Y,POSITION_Z,SPEED_VX,SPEED_VY,SPEED_VZ ,OBS_TIME) values";
    private String valueSql = "('%s','%s','%s','%s','%s','%s','%s')";

    //查询 数据步长
    private String queryStepSql ="select OBS_TIME from COLLECT_GNSS_%s where  OBS_TIME BETWEEN '%s' and '%s' limit 2";

    //查询数据
    private String queryGnnsSql = "select OBS_TIME,POSITION_X,POSITION_Y,POSITION_Z,SPEED_VX,SPEED_VY,SPEED_VZ from COLLECT_GNSS_%s where  OBS_TIME BETWEEN '%s' and '%s' " +
            "order by OBS_TIME asc";
    //陨落数据
    private String queryFallSql = "select EPOCH_TIME as OBS_TIME,PX as POSITION_X,PY as POSITION_Y,PZ as POSITION_Z,SX as SPEED_VX,SY as SPEED_VY," +
            "SZ as SPEED_VZ from COLLECT_FALL_RECORD where SATELLITE ='%s' and  EPOCH_TIME BETWEEN '%s' and '%s' " +
            "order by EPOCH_TIME asc";




    /**
     * 保存卫星数据
     * @param satellite
     * @param list
     */
    public void saveData(String satellite, List<TrackDataBO> list) {

        if (!StringUtils.hasLength(satellite)) {
            throw new ServiceException("卫星名字不得为空");
        }

        String tableName = "COLLECT_GNSS_"+satellite.toUpperCase();

        dbCheck(tableName);

        List<List<TrackDataBO>> split = CollectionUtil.split(list, 1000);
        for (List<TrackDataBO> part : split) {
            String insert = String.format(columnSql, tableName);
            if (part.size()==1) {
                TrackDataBO bo = part.get(0);
                jdbcTemplate.execute(insert +String.format(valueSql,bo.getPosition_x(),bo.getPosition_y(),bo.getPosition_z(),bo.getSpeed_vx(),bo.getSpeed_vy(),bo.getSpeed_vz(), DateUtil.format(bo.getObs_time(),"yyyy-MM-dd HH:mm:ss")));
            }else {
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append(insert);
                for (TrackDataBO bo : part) {
                    stringBuffer.append(String.format(valueSql,bo.getPosition_x(),bo.getPosition_y(),bo.getPosition_z(),bo.getSpeed_vx(),bo.getSpeed_vy(),bo.getSpeed_vz(),DateUtil.format(bo.getObs_time(),"yyyy-MM-dd HH:mm:ss")));
                    stringBuffer.append(",");
                }
                //去掉最后的逗号
                stringBuffer.deleteCharAt(stringBuffer.length()-1);
                jdbcTemplate.execute(stringBuffer.toString());
            }
        }
    }

    /**
     * 监测是否需要创建表,若需要则创建
     * @param tableName
     */
    public  void dbCheck(String tableName) {
        try {
            jdbcTemplate.execute(String.format(checkSql,tableName));
            log.info("监测表已存在:{}",tableName);
        }catch (BadSqlGrammarException e) {
            try {
                log.info("创建表:{}",tableName);
                createDb(tableName);
            }catch (Exception ee) {
                log.error("",ee);
                throw new ServiceException("创建表异常");
            }
            //if (cause instanceof DMException) {
            //    DMException dm = (DMException) cause;
            //    if (-2106 == dm.getErrorCode()) {
            //        //无效的表或视图 去创建
            //        log.info("监测表不存在,去创建:{}",tableName);
            //        createDb(tableName);
            //    }else {
            //        throw new ServiceException("创建表异常");
            //    }
            //}
        }catch (Exception e) {
            log.error("",e);
            throw new ServiceException("表异常");
        }

    }

    /**
     * 创建序列,建表,索引
     * @param tableName
     */
    private void createDb(String tableName) {

        jdbcTemplate.execute(String.format(creatDbSql,tableName,tableName));
        jdbcTemplate.execute(String.format(commont1,tableName));
        jdbcTemplate.execute(String.format(commont2,tableName));
        jdbcTemplate.execute(String.format(commont3,tableName));
        jdbcTemplate.execute(String.format(commont4,tableName));
        jdbcTemplate.execute(String.format(commont5,tableName));
        jdbcTemplate.execute(String.format(commont6,tableName));
        jdbcTemplate.execute(String.format(commont7,tableName));
        jdbcTemplate.execute(String.format(commont8,tableName));
        try {
            jdbcTemplate.execute(String.format(indexSql, tableName, tableName));
        }catch (Exception e) {
            log.info("索引已存在");
        }
    }


    public  List<CollectGnssPO> queryGnns(String type, String satellite, Date startTime, Date endTime) {

        //"任务类型 交会对接 inter;在轨 on;目标陨落 fall")
        String sql = "";
        if ("fall".equals(type)) {
            sql = String.format(queryFallSql, satellite.toUpperCase(), startTime, endTime);
            //System.out.println(sql);
        }else {
            sql = String.format(queryGnnsSql, satellite.toUpperCase(), startTime, endTime);
        }
        List<CollectGnssPO> gnnsPos = new ArrayList<>();
        try {
            gnnsPos = jdbcTemplate.query(sql, new RowMapper<CollectGnssPO>() {
                @Override
                public CollectGnssPO mapRow(ResultSet rs, int rowNum) throws SQLException {
                    CollectGnssPO po = new CollectGnssPO();
                    //OBS_TIME,POSITION_X,POSITION_Y,POSITION_Z,SPEED_VX,SPEED_VY,SPEED_VZ
                    po.setObs_time(rs.getTimestamp("obs_time"));
                    po.setPosition_x(new BigDecimal(rs.getString("position_x")));
                    po.setPosition_y(new BigDecimal(rs.getString("position_y")));
                    po.setPosition_z(new BigDecimal(rs.getString("position_z")));
                    po.setSpeed_vx(new BigDecimal(rs.getString("speed_vx")));
                    po.setSpeed_vy(new BigDecimal(rs.getString("speed_vy")));
                    po.setSpeed_vz(new BigDecimal(rs.getString("speed_vz")));
                    return po;
                }
            });
        }catch (Exception e){
            e.printStackTrace();
            throw new ServiceException("gnss数据查询异常,数据为空");

        }

        //移除时间重复的
        Map<Date, List<CollectGnssPO>> collect = gnnsPos.stream().collect(Collectors.groupingBy(CollectGnssPO::getObs_time));
        List<CollectGnssPO> resList = new ArrayList<>();
        for (Map.Entry<Date, List<CollectGnssPO>> dateListEntry : collect.entrySet()) {
            resList.add(dateListEntry.getValue().get(0));
        }
        List<CollectGnssPO> sortedList = resList.stream().sorted((a, b) -> {
            return DateUtil.compare(a.getObs_time(), b.getObs_time());
        }).collect(Collectors.toList());
        log.info("返回GNSS 数据:{}",sortedList.size());
        return sortedList;
    }


    /**
     * 算法配置文件中 数据步长
     * @param gnssPOS
     * @return
     */
    public int queryStep(List<CollectGnssPO> gnssPOS) {
        //gnnssPos 已经是排好序的
        CollectGnssPO start = gnssPOS.get(0);
        CollectGnssPO end = gnssPOS.get(1);
        int step = (int)DateUtil.between(start.getObs_time(),end.getObs_time(),DateUnit.SECOND);
        return  step;
    }
}
